package db;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

import java.util.logging.Level;
import java.util.logging.Logger;


/**
 * 
 * @author giambattista
 *
 */
public class DbUtils {
//public enum DbUtils {
//	INSTANCE;
	
	/** */
	Logger logger = Logger.getLogger("db.DbUtils");
	
	/** */
	private String connectionString   = "jdbc:oracle:thin:@localhost:1521:XE";
	
	/** */
	private String connectionUser 	  = "EASY_OWN";
	
	/** */
	private String connectionPassword = "easydoc";
	
	/** */
	private Connection connection     = null;
	
	////////////////////////////////////////////////////////
	private static DbUtils instance   = null;
	////////////////////////////////////////////////////////

	/**
	 * 
	 */
	private DbUtils() {
		try {

			Class.forName("oracle.jdbc.driver.OracleDriver");
			System.out.println("Oracle JDBC Driver Registered!");
		} catch (Exception e) {
			//P
			System.out.println("Where is your Oracle JDBC Driver?");
			//TODO log
			e.printStackTrace();
			return;
		}
	}

	////////////////////////////////////////////////////////	
	public static DbUtils getInstance() {
		if (instance == null)
			instance = new DbUtils();
		return instance;
	}
	//////////////////////////////////////////////////////// 
	
	/**
	 * 
	 * @return
	 * @throws SQLException
	 */
	private Connection getConnection() throws SQLException {
		if (connection != null && isValid(connection))
			return connection;

		connection = DriverManager.getConnection(connectionString,
				connectionUser, connectionPassword);

		return connection;
	}

	/**
	 * 
	 * @param c
	 * @return
	 */
	private boolean isValid(Connection c) {
		Statement stmt = null;
		ResultSet rs = null;
		try {
			stmt = c.createStatement();
			// oracle
			rs = stmt.executeQuery("SELECT 1 FROM Dual");
			// others
			// rs = stmt.executeQuery("SELECT 1");
			if (rs.next()) {
				return true; // connection is valid
			}
		} catch (SQLException e) {
			//log the exception ...
			logger.log(Level.SEVERE, "SQL Exception", e);
			return false;
		}
		return false;
	}

	/**
	 * 
	 * @param fieldId
	 * @param fieldName
	 * @return
	 */
	public String getFieldHostCode(String fieldId, String fieldName) {								//PUBLIC
		String hostCode = "";
		try {

			Connection connection = null;
			connection = getConnection();

			Statement statement = connection.createStatement();

			String query = "select KV_HOST_KEY from T_EASY_KNOW_VARFIELDS where KV_TYPE='L' AND KV_ID = "
					+ fieldId + " AND KV_LABEL= '" + fieldName + "'";
			//P
			System.out.println("--Query: "+query);
			ResultSet resultset = statement.executeQuery(query);
			while (resultset.next()) {
				hostCode = resultset.getString(1);
			}
		} catch (Exception e) {
			e.printStackTrace();
		}
		return hostCode;
	}

	/**
	 * 
	 * @param args
	 */
	public static void main(String args[]) {
		DbUtils util = DbUtils.getInstance();
		String fieldId = "1379819";
		String fieldName = "ABI_BANCA_RENDICONTAZIONE";
		//P
		System.out.println(util.getFieldHostCode(fieldId, fieldName));
	}
}

